﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_UtilityAssetTransformers_SelectForGrid')
	DROP PROCEDURE up_UtilityAssetTransformers_SelectForGrid
GO


CREATE PROCEDURE [dbo].[up_UtilityAssetTransformers_SelectForGrid]
	 @UtilityId int 
	,@PageSize int = 100
	,@PageNumber int = 1
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
AS

SET NOCOUNT ON	

DECLARE @StartRowNumber int
DECLARE @EndRowNumber int

SET @StartRowNumber = ((@PageNumber - 1) * @PageSize) + 1
SET @EndRowNumber = @PageNumber * @PageSize


DECLARE @table table
(
	 RowNumber int
	,TotalCount int
	,Utility_Asset_TransformerId int
	,UtilityFK int
	,Vendor_ProjectFK int
	,Latitude float
	,Longitude float
	,CreatedDate datetime
	,SerialNumber varchar(25)
	,ImageFileName nvarchar(250)
	,FriendlyName nvarchar(100)
	,Comments ntext
)
	
INSERT @table
(
	 [RowNumber]
	,[TotalCount]
	,[Utility_Asset_TransformerId]
	,[UtilityFK]
	,[Vendor_ProjectFK]
	,[Latitude]
	,[Longitude]
	,[CreatedDate]
	,SerialNumber
	,ImageFileName
	,FriendlyName
	,Comments
)
SELECT 
*
FROM 
(
	SELECT 
		 ROW_NUMBER() OVER (ORDER BY Utility_Asset_TransformerId) AS RowNumber
		,COUNT(Utility_Asset_TransformerId) OVER (PARTITION BY NULL) AS TotalCount
		,[Utility_Asset_TransformerId]
		,[UtilityFK]
		,[Vendor_ProjectFK]
		,[Latitude]
		,[Longitude]
		,[CreatedDate]
		,SerialNumber
		,ImageFileName
		,FriendlyName
		,Comments
	from v_tb_Utility_Asset_Transformers
	WHERE
		[UtilityFK] = @UtilityId
) AS Data
WHERE RowNumber BETWEEN @StartRowNumber AND @EndRowNumber

-- primary select
SELECT * FROM @table


